﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using RosarioFinanzas.ar.com.rofex.ws;
using System.Web.Services;
using Generico.Coneccion;
using System.Configuration;
using System.Globalization;

namespace RosarioFinanzas
{
    public partial class Formulario_web1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Response.Expires = 0;
            var cn = new ClsConeccion();
            cn.Abrir();
            if (this.Request.QueryString["M"] != null)
            {
                cn.AgregarParametros("Moneda", Request.QueryString["M"].ToString());
            }
            else
            {
                cn.AgregarParametros("Moneda", "D");
            }

            var sqldr = cn.EjecutarSpDataReader("spGetBancosActivas2");
            var dt = new DataTable();
            dt.Load(sqldr);
            DDL1.DataSource = dt;
            DDL1.DataTextField = "varBanco";
            DDL1.DataValueField = "idBanco";
            this.DDL1.DataBind();
          
            DDL2.DataSource = dt;
            DDL2.DataTextField = "varBanco";
            DDL2.DataValueField = "idBanco";
            this.DDL2.DataBind();

            cn.Cerrar();
            var cn1 = new ClsConeccion();
            cn1.Abrir();
            if (this.Request.QueryString["M"] != null)
            {
                cn1.AgregarParametros("Moneda", Request.QueryString["M"].ToString()); 
            }
            else
            {
                cn1.AgregarParametros("Moneda", "D");
            }
            var sqldr1 = cn1.EjecutarSpDataReader("spGetBancosActivasMas2");
            var dt1 = new DataTable();
            dt1.Load(sqldr1);
            
            DDL3.DataSource = dt1;
            DDL3.DataTextField = "varBanco";
            DDL3.DataValueField = "idBanco";
            

            this.DDL3.DataBind();
            
            cn1.Cerrar();
        }

        [WebMethod]
        public static string strFecha(string str)
        {

            string[] dayarray = { "Domingo", "Lunes", "Martes", "Miércoles", "Jueves", "Viernes", "Sábado" };
            string[] montharray = { "Enero", "Febrero", "Marzo", "Abril", "Mayo", "Junio", "Julio", "Agosto", "Septiembre", "Octubre", "Noviembre", "Diciembre" };
            var daym = DateTime.Today.Day.ToString();
            var year = DateTime.Today.Year.ToString();
            var day = Convert.ToInt32(DateTime.Today.DayOfWeek);
            var month = DateTime.Today.Month - 1;
            return dayarray[day] + ", " + daym + " de " + montharray[month] + " del " + year + " ";

        }
        
    
         [WebMethod]
        public static string BCRCotizaciones(string str)
        {
            return PizarraBCR();
        } 


        [WebMethod]
        public static string BCRHistorico(string txtFecha)
        {
            var cn = new ClsConeccion();
            cn.Abrir();
            string sql = "SELECT  convert(varchar, Fecha  , 103) FECHA1 , varDescripcion NOMBRE, MontoHoy HOY, MontoAyer AYER,0 [VARIACION $],";
            sql += " 0 [VARIACION %] FROM dbo.BCRCotizacion BCR";
            sql += " INNER JOIN dbo.Indice I ON I.intIndice = BCR.intIdIndice  where Fecha>='" + (FormatoAnsiFecha(txtFecha) + " 00:00:00") + "' And Fecha<='" + (FormatoAnsiFecha(txtFecha) + " 23:59:59") + "' order by Fecha desc";             
            var sqldr2 = cn.EjecutarSqlDataReader(sql);
            var dt = new DataTable();
            dt.Load(sqldr2);   
            cn.Cerrar();
            string html = PizarraBCR();
            html += "<div class=\"PizarraFondo\">";
            html += "<div style=\"clear:both;  padding-top:10px; padding-bottom:0px; padding-left:0px; padding-right:0px;background-color:white;\">";
            html += "<div style=\"padding-left:8px;padding-bottom:10px;float:left;font-family:Arial;font-size:13px; color:#7d7d7f;\">Cotización del día " + txtFecha + " </div> ";
            html += "<div style=\"padding-bottom:10px;background-color:white;float:right\"><a style=\"border:0;color:black;\" href=\"javascript:popPDF('BCRH1','" + txtFecha + "','','','','');\"><img style=\"border:0;height:15px;\" src=\"Imagenes/impresora.png\" ></a> </div>";
            html += "<div style=\" clear:both;height:2px; width:100%; background-color:#7d7d7f;\"></div>";
            html += "<div style=\" clear:both;height:2px; width:100%; background-color:white;\"></div>";
            html += "</div>";
            html += PizarraHistorica(dt);
            html += "</div>";
            return html;
        }


        [WebMethod]
        public static string BCRComparacion(string txtFechaDesde, string txtFechaHasta)
        {
            var cn = new ClsConeccion();
            cn.Abrir();
            string sql = "SELECT  convert(varchar, Fecha  , 103) FECHA1 , varDescripcion NOMBRE, MontoHoy HOY, MontoAyer AYER,0 [VARIACION $],";
            sql += " 0 [VARIACION %] FROM dbo.BCRCotizacion BCR";
            sql += " INNER JOIN dbo.Indice I ON I.intIndice = BCR.intIdIndice  where Fecha>='" + (FormatoAnsiFecha(txtFechaDesde) + " 00:00:00") + "' And Fecha<='" + (FormatoAnsiFecha(txtFechaDesde) + " 23:59:59") + "'";
            sql += " OR Fecha>='" + (FormatoAnsiFecha(txtFechaHasta) + " 00:00:00") + "' And Fecha<='" + (FormatoAnsiFecha(txtFechaHasta) + " 23:59:59") + "' order by Fecha desc";
            var sqldr2 = cn.EjecutarSqlDataReader(sql);
            var dt = new DataTable();
            dt.Load(sqldr2);
            cn.Cerrar();
            string html = PizarraBCR();
            html += "<div class=\"PizarraFondo\">";
            html += "<div style=\"clear:both;  padding-top:10px; padding-bottom:0px; padding-left:0px; padding-right:0px;background-color:white;\">";
            html += "<div style=\"padding-left:8px;padding-bottom:10px;float:left;font-family:Arial;font-size:13px; color:#7d7d7f;\">Comparación del día " + txtFechaDesde + " y " + txtFechaHasta + " </div> ";
            html += "<div style=\"padding-bottom:10px;background-color:white;float:right\"><a style=\"border:0;color:black;\" href=\"javascript:popPDF('BCRC','" + txtFechaDesde + "','" + txtFechaHasta + "','','','');\"><img style=\"border:0;height:15px;\" src=\"Imagenes/impresora.png\" ></a> </div>";
            html += "<div style=\" clear:both;height:2px; width:100%; background-color:#7d7d7f;\"></div>";
            html += "<div style=\" clear:both;height:2px; width:100%; background-color:white;\"></div>";
            html += "</div>";
            html += PizarraHistorica(dt);
            html += "</div>";
            return html;
        }


        [WebMethod]
        public static string BCRHistoticoDesdeHasta(string txtFechaDesde, string txtFechaHasta)
        {
            var cn = new ClsConeccion();
            cn.Abrir();
            string sql = "SELECT  convert(varchar, Fecha  , 103) FECHA1 , varDescripcion NOMBRE, MontoHoy HOY, MontoAyer AYER,0 [VARIACION $],";
            sql += " 0 [VARIACION %] FROM dbo.BCRCotizacion BCR";
            sql += " INNER JOIN dbo.Indice I ON I.intIndice = BCR.intIdIndice  where Fecha>='" + (FormatoAnsiFecha(txtFechaDesde) + " 00:00:00") + "' And Fecha<='" + (FormatoAnsiFecha(txtFechaHasta) + " 23:59:59") + "' order by Fecha desc";
            var sqldr2 = cn.EjecutarSqlDataReader(sql);
            var dt = new DataTable();
            dt.Load(sqldr2);
            cn.Cerrar();
            string html = PizarraBCR();
            html += "<div class=\"PizarraFondo\">";
            html += "<div style=\"clear:both;  padding-top:10px; padding-bottom:0px; padding-left:0px; padding-right:0px;background-color: #C0C0C0;\">";
            html += "<div style=\"padding-left:8px;padding-bottom:10px;float:left;font-family:Arial;font-size:13px; color:#FFFFFF;\">Cotizaciones entre " + txtFechaDesde + " y " + txtFechaHasta + " </div> ";
            html += "<div style=\"padding-bottom:10px;background-color:#C0C0C0;float:right\"><a style=\"border:0;color:black;\" href=\"javascript:popPDF('BCRH','" + txtFechaDesde + "','" + txtFechaHasta + "','','','');\"><img style=\"border:0;height:15px;\" src=\"Imagenes/impresora.png\" ></a> </div>";
            html += "<div style=\" clear:both;height:2px; width:100%; background-color: #C0C0C0;\"></div>";
            html += "<div style=\" clear:both;height:2px; width:100%; background-color: #C0C0C0;\"></div>";
            html += "</div>";
            html += PizarraHistorica(dt);
            html += "</div>";
            return html;
        }



        [WebMethod]
        public static string BCRUltimoMes(string nada)
        {

            string txtFecha = DateTime.Now.AddDays(-30).ToShortDateString();

            var cn = new ClsConeccion();
            cn.Abrir();
            string sql = "SELECT  convert(varchar, Fecha  , 103) FECHA1 , varDescripcion NOMBRE, MontoHoy HOY, MontoAyer AYER,0 [VARIACION $],";
            sql += " 0 [VARIACION %] FROM dbo.BCRCotizacion BCR";
            sql += " INNER JOIN dbo.Indice I ON I.intIndice = BCR.intIdIndice  where Fecha>= convert(datetime,'" + txtFecha + "', 105) order by Fecha desc";
            var sqldr2 = cn.EjecutarSqlDataReader(sql);
            var dt = new DataTable();
            dt.Load(sqldr2);
            cn.Cerrar();
            string html = PizarraBCR();
            html += "<div class=\"PizarraFondo\">";
            html += "<div style=\"clear:both;  padding-top:10px; padding-bottom:0px; padding-left:0px; padding-right:0px;background-color:white;\">";
            html += "<div style=\"padding-left:8px;padding-bottom:10px;float:left;font-family:Arial;font-size:13px; color:#7d7d7f;\">Cotizaciones de los últimos 30 días</div> ";
            html += "<div style=\"padding-bottom:10px;background-color:white;float:right\"><a style=\"border:0;color:black;\" href=\"javascript:popPDF('BCRH1','" + txtFecha + "','','','','');\"><img style=\"border:0;height:15px;\" src=\"Imagenes/impresora.png\" ></a> </div>";
            html += "<div style=\" clear:both;height:2px; width:100%; background-color:#7d7d7f;\"></div>";
            html += "<div style=\" clear:both;height:2px; width:100%; background-color:white;\"></div>";
            html += "</div>";
            html += PizarraHistorica(dt);
            html += "</div>";
            return html;
        }



        private static string PizarraBCR()
        {
            string html=String.Empty;
            try
            {
                var cn = new ClsConeccion();
                cn.Abrir();

                var sqldr2 = cn.EjecutarSpDataReader("sp_GETBCRPIZARRA"); 
                var dt = new DataTable();
                dt.Load(sqldr2);
                cn.Cerrar();
                if (dt.Rows.Count > 0)
                {
                    html += "<div  style=\" font-family:Arial; margin-top: 0px; background-image: url(" + "Imagenes/BaseBCR.png" + ");";
                    html += "height: 190px; padding-top: 15px; padding-right: 5px;  padding-left: 5px;\">";
                    html += "<div style=\"height:18px;\"><div style=\"float:left;font-family:Arial;font-size:15px; color:white;\"> Cotizaciones de pizarra </div> ";
                    html += "<div style=\"float:right\"><a style=\"border:0;color:black;text-decoration:none;\" href=\"http://www.bcr.com.ar/Pages/Publicaciones/bdGranos.aspx\" target=\"_blank\"><img style=\"border:0;height:15px;\" src=\"Imagenes/impresora.png\" ></a> </div></div>";
                    html += "<table cellpadding=\"1\" cellspacing=\"2\" style=\"margin-top:12px; height: 86px; clear:both;\" > <tr> ";

                    foreach (DataColumn dc in dt.Columns)
                    {
                        html += "<td style=\"width:80px; height: 20px; text-align: center;color: black; font-size:9px;font-family:Arial;\">" + dc.ColumnName + "</TD>";
                    }
                    int c = dt.Columns.Count;
                    html += "</tr>";
                    foreach (DataRow dr in dt.Rows)
                    {
                        html += "<tr>";
                        for (int i = 0; i < c; i++)
                        {
                            html += "<td style=\"width:82px; height: 20px; background-color:";
                            switch (i)
                            {
                                case 1:
                                    html += "#2D2F44;";
                                    break;
                                case 2:
                                    html += "#2C8291;";
                                    break;
                                default:
                                    html += "#365C69;";
                                    break;

                            }
                            
                            html += " font-size:10px;text-align: center;color: white;font-family:Arial;\">";
                            switch (dr[i].GetType().Name)
                            {
                                case "Decimal":
                                    html += Convert.ToDecimal(dr[i]).ToString("#,##0.00");
                                    break;
                                default:
                                    html += dr[i];
                                    break;

                            }
                        }
                        html += "</tr>";
                    }
                
                    html += "</table>";
                    html += "</div>";
                }    
                return html;
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }
                 
        private static string PizarraBCRs()
        {
            string html=String.Empty;
            try
            {
                var cn = new ClsConeccion();
                cn.Abrir();
                string sql = "SELECT varDescripcion NOMBRE, MontoHoy HOY, MontoAyer AYER,MontoHoy-MontoAyer[VARIACION $],";
                sql +=" ((MontoHoy-MontoAyer)*100)/MontoHoy  [VARIACION %] FROM dbo.BCRCotizacion BCR";
                sql +=" INNER JOIN (SELECT intIdIndice, max(Fecha) Fecha FROM BCRCotizacion";
                sql +=" GROUP BY intIdIndice) AS B ON BCR.intIdIndice = B.intIdIndice AND BCR.Fecha = B.Fecha";
                sql += " INNER JOIN dbo.Indice I ON I.intIndice = BCR.intIdIndice";
                var sqldr2 = cn.EjecutarSqlDataReader(sql);
                var dt = new DataTable();               
                dt.Load(sqldr2);               
                cn.Cerrar();
                if (dt.Rows.Count > 0)
                {
                    html += "<div  style=\" font-family:Arial; margin-top: 0px; background-image: url(" + "Imagenes/BaseBCR.png" + ");";
                    html += "height: 190px; padding-top: 15px; padding-right: 5px;  padding-left: 5px;\">";
                    html += "<div style=\"height:18px;\"><div style=\"float:left;font-family:Arial;font-size:15px; color:white;\"> Cotizaciones de pizarra </div> ";
                    html += "<div style=\"float:right\"><a style=\"border:0;color:black;text-decoration:none;\" href=\"http://www.bcr.com.ar/Pages/Publicaciones/bdGranos.aspx\" target=\"_blank\"><img style=\"border:0;height:15px;\" src=\"Imagenes/impresora.png\" ></a> </div></div>";
                    html += "<table cellpadding=\"1\" cellspacing=\"2\" style=\"margin-top:12px; height: 86px; clear:both;\" > <tr> ";

                    foreach (DataColumn dc in dt.Columns)
                    {
                        if ((dc.ColumnName).ToLower() == "hoy") dc.ColumnName = DateTime.Now.ToShortDateString();
                        if ((dc.ColumnName).ToLower() == "ayer") dc.ColumnName = DateTime.Now.AddDays(-1).ToShortDateString();                        

                        html += "<td style=\"width:80px; height: 20px; text-align: center;color: black; font-size:9px;font-family:Arial;\">" + dc.ColumnName + "</TD>";
                    }
                    int c = dt.Columns.Count;
                    html += "</tr>";
                    foreach (DataRow dr in dt.Rows)
                    {
                        html += "<tr>";
                        for (int i = 0; i < c; i++)
                        {
                            html += "<td style=\"width:82px; height: 20px; background-color:";
                            switch (i)
                            {
                                case 1:
                                    html += "#2D2F44;";
                                    break;
                                case 2:
                                    html += "#2C8291;";
                                    break;
                                default:
                                    html += "#365C69;";
                                    break;

                            }
                            
                            html += " font-size:10px;text-align: center;color: white;font-family:Arial;\">";
                            switch (dr[i].GetType().Name)
                            {
                                case "Decimal":
                                    html += Convert.ToDecimal(dr[i]).ToString("#,##0.0000");
                                    break;
                                default:
                                    html += dr[i];
                                    break;

                            }
                        }
                        html += "</tr>";
                    }
                
                    html += "</table>";
                    html += "</div>";
                }    
                return html;
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }
        private static string PizarraHistorica(DataTable dt)
        {
            string html = "" ;
            html += "<table  cellspacing=\"0\" style=\"background-color:white;width:100%;\"><tr class=\"PizarraTitulo\"><td>FECHA</TD><td>MAIZ DURO</TD><td>SOJA</TD><td>TRIGO</TD><td>GIRASOL</TD><td style=\"border-right-color:#7d7d7f;\">SORGO</TD> ";
            string fechaaux = "";
            foreach (DataRow dr in dt.Rows)
            {
                if (dr[0].ToString() != fechaaux)
                {
                    fechaaux = dr[0].ToString();
                    html += "</tr><tr class=\"PizarraDatos\"><td>" + fechaaux + "</td>";
                    html += "<td>" + Convert.ToDecimal(dr[2]).ToString("#,##0.00") + "</td>";
                }
                else
                {
                    html += "<td>" + Convert.ToDecimal(dr[2]).ToString("#,##0.00") + "</td>";
                }
            }
            html+="</tr></table>";
            return html;
        }

        #region Rofex

         [WebMethod]
         public static string MenuRofex(string str)
         {
             var obj = new ROFEXWebService();
             var ds = obj.Productos();
             str = "";
             foreach (DataRow dr in ds.Tables[0].Rows)
             {
                 var id = dr["IdProducto"].ToString();
                 var Nombre = dr["Nombre"].ToString();
                 str += "<li><a href=\"javascript:ROFEXPRODUCTOS('" + id + "','" + palabras(Nombre) + "');\">" + palabras(Nombre) + " </a></li>";

             }

             return str;
         }
        private static string palabras(string str)
        {
            int c = 1;
            int pos = 0;
            for (int i = 0; i < str.Length; i++)
            {
                if (str[i] == ' ')
                {
                    pos = i;
                    c++;
                    if (c > 2) break;
                }
            }
            if (c > 2)
            {
                str = str.Substring(0, pos);
            }
            return str;
        }

        private static decimal unidadProducto(string idProducto)
        {
            switch (idProducto)
            {
                case "1":
                    return 30;
                case "2":
                    return 30;
                case "3":
                    return 30;
                case "6":
                    return 30;
                case "18":
                    return 1000;
                case "35":
                    return 1;
                case "40":
                    return 10;
                case "5":
                    return 5;
                case "45":
                    return 5;
                case "22":
                    return 1000;
                case "55":
                    return 10000;
            }
            return 1;

        }
        [WebMethod]
        public static string IndiceRofex(string idProducto, string strProducto)
        {
            var obj = new ROFEXWebService();
            var ds = obj.MarketData(1, DateTime.Today, DateTime.Today, "", Convert.ToInt32(idProducto));
        

            string html = "";
            html += "<div id=\"datos\" style=\"margin-top: -35px; background-color: #f2f1ed; padding: 12px 3px 26px 3px;\">";
            html += "<div style=\"clear:both;width:100%;margin-top: 0px;margin-bottom: 2px; height: 26px; background-color:#5a6845;\"> ";
            html += "<div  style=\"float:left; font-family:Arial;";
            html += " padding-right: 15px; padding-left: 15px; padding-top: 0px;  line-height: 26px;  height: 26px; color:white; font-size:14px;\">";
            html += strProducto + "  /ROFEX  </div> ";
            html += "<div style=\"float:right;line-height: 26px;padding-top: 5px;padding-right: 10px;\"><a style=\"border:0;color:black;text-decoration:none;\" href=\"javascript:popPDF('R','" + idProducto + "','" + strProducto + "','','','');\"><img style=\"border:0;height:15px;\" src=\"Imagenes/impresora.png\" ></a> </div> </div>";
            html += "<div  style=\"clear:both;margin-top: 0px;margin-bottom: 10px; background-color:#5a6845; height:2px;\"></div>";


            DataTable dt = new DataTable();
            dt.Columns.Add("Vencimiento", DateTime.Today.GetType());
            dt.Columns.Add("Posicion", "".GetType());
            dt.Columns.Add("Precio", 2.3.GetType());
            dt.Columns.Add("AjusteAnterior", 2.3.GetType());
            dt.Columns.Add("Variacion", 2.3.GetType());
            dt.Columns.Add("Volumen", 2.3.GetType());
            dt.Columns.Add("Fecha", DateTime.Today.GetType());

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                DataRow[] dr1 = ds.Tables[1].Select("Posicion = '" + dr["Posicion"].ToString() + "'");
                dt.Rows.Add(
                    dr["Vencimiento"],
                    dr["Posicion"],
                    dr1[0]["Precio"],
                    dr1[0]["AjusteAnterior"],
                    dr1[0]["Variacion"],
                    dr1[0]["Volumen"],
                    dr1[0]["Fecha"]
                    );
            }

            var Dia = DateTime.Today.DayOfWeek;
            var h = DateTime.Now.Hour;

            IEnumerable<DataRow> query = null;
            if (h >= 10 && h <= 18 && DayOfWeek.Saturday != Dia && DayOfWeek.Sunday != Dia)
            {
                query =
                from c1 in dt.AsEnumerable()
                where c1.Field<DateTime>("Fecha") >= DateTime.Today
                orderby c1.Field<DateTime>("Vencimiento") ascending
                select c1;
            }
            else
            {
                query =
                from c1 in dt.AsEnumerable()
                orderby c1.Field<DateTime>("Vencimiento") ascending
                select c1;
            }


            DataTable boundTable = query.CopyToDataTable<DataRow>();

            html += "<div style=\"margin-top: 0px; background-color: #f2f1ed; padding-top: 0px; padding-bottom: 0px;\">";
            html += "<table cellpadding=\"1\" cellspacing=\"2\" style=\"background-color: #f2f1ed;border-style:solid;";
            html += "border-width:3px; border-color:#f2f1ed;\">";
            html += "<tr><td style=\"width: 100px; height: 20px; text-align: center;color: black;font-size:9px;font-family:Arial;\">POSICION</TD>";
            html += "<td style=\"width: 70px; height: 20px; text-align: center;color: black;font-size:9px;font-family:Arial;\">PRECIO</TD>";
            html += "<td style=\"width: 80px; height: 20px; text-align: center;color: black;font-size:9px;font-family:Arial;\">AJUSTE ANT.</TD>";
            html += "<td style=\"width: 70px; height: 20px; text-align: center;color: black;font-size:9px;font-family:Arial;\">VARIACION</TD>";
            html += "<td style=\"width: 80px; height: 20px; text-align: center;color: black;font-size:9px;font-family:Arial;\">VARIACION %</TD>";
            html += "<td style=\"width: 70px; height: 20px; text-align: center;color: black;font-size:9px;font-family:Arial;\">VOLUMEN</TD>";
            html += "<td style=\"width: 80px;height: 20px; text-align: center;color: black;font-size:9px;font-family:Arial;\">FECHA</TD></tr></table>";
            
             int c = 0;
             foreach (DataRow dr in boundTable.Rows)
             {
                 if (dr["Volumen"].ToString().Length > 0 && Convert.ToDecimal(dr["Volumen"]) > 0)
                 {
                     string Fecha_text = dr["Fecha"].ToString().Substring(0, 10);
                     var Posicion = dr["Posicion"].ToString();
                     var Precio = (dr["Precio"].ToString().Length > 0) ? (Convert.ToDecimal(dr["Precio"].ToString())).ToString("f4") : "---";
                     var AjusteAnterior = (dr["AjusteAnterior"].ToString().Length > 0) ? (Convert.ToDecimal(dr["AjusteAnterior"].ToString())).ToString("f4") : "---";
                     var Variacion = (dr["Variacion"].ToString().Length > 0) ? (Convert.ToDecimal(dr["Variacion"].ToString())).ToString("f4") + "" : "---";
                     string VariacionPorcentaje = "";

                     try
                     {
                         var decAjuste = (dr["AjusteAnterior"].ToString().Length > 0) ? Convert.ToDecimal(dr["AjusteAnterior"].ToString()) : 0;
                         var decPrecio = (dr["Precio"].ToString().Length > 0) ? Convert.ToDecimal(dr["Precio"].ToString()) : 0;
                         VariacionPorcentaje = ((decPrecio / decAjuste - 1) * 100).ToString("f2");
                     }
                     catch
                     {
                         VariacionPorcentaje = 0.ToString("f2");
                     }
                     //var decAjuste = (dr["AjusteAnterior"].ToString().Length > 0) ? Convert.ToDecimal(dr["Variacion"].ToString()) : 0;
                     //var decPrecio = (dr["Precio"].ToString().Length>0)?Convert.ToDecimal(dr["Precio"].ToString()):0;
                     //var VariacionPorcentaje = ((decPrecio / decAjuste - 1) * 100).ToString("f2"); 
                     var Volumen = (dr["Volumen"].ToString().Length > 0) ? (Convert.ToDecimal(dr["Volumen"].ToString()) / unidadProducto(idProducto)).ToString("N0") : "---";


                     html += "<div style=\"margin-top: 0px; background-color: #f2f1ed; padding-top: 5px; padding-bottom: 5px;\">";
                     html += "<table cellpadding=\"1\" cellspacing=\"2\" style=\"background-color: white;border-style:solid;";
                     html += "border-width:3px; border-color:White;\">";
                     html += "<tr><td style=\"width: 100px; height: 20px; background-color: white; text-align: center;color: black;font-size:9px;font-family:Arial;\">";
                     html += Posicion + "</td>";
                     html += "<td style=\"width:70px; height: 20px; background-color: #7f7f80;  text-align: center;color: white;font-size:9px;font-family:Arial;\">";
                     html += Precio + "</td>";
                     html += "<td style=\"width: 80px; height: 20px; background-color: silver; text-align: center;color: black;font-size:9px;font-family:Arial;\">";
                     html += AjusteAnterior + "</td>";
                     html += "<td style=\"width: 70px; height: 20px; background-color: silver; text-align: center;color: black;font-size:9px;font-family:Arial;\">";
                     html += Variacion + "</td>";
                     html += "<td style=\"width: 80px; height: 20px; background-color: silver; text-align: center;color: black;font-size:9px;font-family:Arial;\">";
                     html += VariacionPorcentaje + "</td>";
                     html += "<td style=\"width: 70px; height: 20px; background-color: silver; text-align: center;color: black;font-size:9px;font-family:Arial;\">";
                     html += Volumen + "</td>";
                     html += "<td style=\"width: 80px; height: 20px; background-color: white; text-align: center;color: black;font-size:9px;font-family:Arial;\">";
                     html += Fecha_text + "</td></tr></table></div>";
                     c++;
                 }
             }
             html += "</div></div>";
            return html;
        }
        #endregion
        #region MervaRos
        [WebMethod]
        public static string MenuMervaRos(string str)
        {
            var cn = new ClsConeccion();
            cn.Abrir();
            var rd = cn.EjecutarSqlDataReader("Select intIndice, varDescripcion from Indice where intRubro = 3");
            var dt = new DataTable();
            dt.Load(rd);
            str = "";
            foreach (DataRow dr in dt.Rows)
            {

                str += "<li><a href=\"javascript:MervaRos('" + dr[0].ToString() + "','" + dr[1].ToString() + "');\">" + dr[1].ToString() + " </a></li>";

            }
            str += "<li><a target=\"_blank\" href=\"http://www.mervaros.com.ar/cotizaciones/merval/panel-merval/\">" + "MERVAL" + " </a></li>";
            str += "<li><a target=\"_blank\" href=\" http://www.mervaros.com.ar/cotizaciones/merval/panel-general/\">" + "Panel Gral." + " </a></li>";
            str += "<li><a target=\"_blank\" href=\" http://www.mervaros.com.ar/cotizaciones/merval/titulos-publicos/\">" + "Titulos Publicos" + " </a></li>";
            str += "<li><a target=\"_blank\" href=\" http://rosariofinanzas.com.ar/EstadisticasMervaros.aspx\">" + "Estadísticas" + " </a></li>";

            cn.Cerrar();

            return str;
        }

        [WebMethod]
        public static string IndiceMervaRos(string idProducto, string strProducto,string fecha)
        {
            try
            {
                string sql = "";
                string sqlFecha = "";
                if (fecha.Length > 0)
                {
                    sqlFecha = "convert(varchar, Fecha  , 103) = '" + fecha + "'";
                }
               
                if (idProducto == "1")
                {
                    if (fecha.Length <= 0)
                    {
                        sqlFecha = "Fecha = (Select max(fecha) from dbo.MervaRosCauciones where intIndice =  " + idProducto + ")";
                    }

                    sql = "Select PLAZO, convert(varchar(5), VTO  , 103)VTO,(convert(varchar(10), [Tasa Ult] * 100) + '%' )as [TASA ULT.],CONCERTADO ,";
                    sql += "  convert(varchar, Fecha  , 103) FECHA From dbo.MervaRosCauciones ";
                    sql += " Where intIndice = " + idProducto;
                    sql += " and " + sqlFecha;
                }
                else
                {
                    if (fecha.Length <= 0)
                    {
                        sqlFecha = "Fecha = (Select max(fecha) from dbo.MervaRosCheques where intidIndice =  " + idProducto + ")";
                    }
                    sql = "Select PLAZO, convert(varchar(5), VTO  , 103)VTO ,(convert(varchar(10), TasaMin * 100) + '%' )as [TASA MIN.],";
                    sql += "(convert(varchar(10), TasaMax * 100) + '%' )as [TASA MAX.],";
                    sql += "(convert(varchar(10), TasaPromedio * 100) + '%' )AS [TASA PROM.], Monto as [MONTO NOM.], ";
                    sql += " convert(varchar, Fecha  , 103) FECHA ";
                    sql += "From MervaRosCheques ";
                    sql += " Where intidIndice = " + idProducto;
                    sql += " and " + sqlFecha;
                }
                var cn = new ClsConeccion();
                cn.Abrir();
                var rd = cn.EjecutarSqlDataReader(sql);
                var dt = new DataTable();
                dt.Load(rd);

                string html = "";

                html += "<div id=\"datos\" style=\" background-color: #f2f1ed; padding: 12px 3px 26px 3px;\">";
                html += "<div style=\"clear:both;width:100%;margin-top: 0px;margin-bottom: 2px; height: 26px; background-color:#761210;\"> ";
                html += "<div  style=\"float:left; font-family:Arial;";
                html += " padding-right: 15px; padding-left: 15px; padding-top: 0px;  line-height: 26px;  height: 26px; color:white; font-size:14px;\">";
                html += strProducto + " /MERVAROS </div> ";
                html += "<div style=\"float:right;line-height: 26px;padding-top: 5px;padding-right: 10px;\"><a style=\"border:0;color:black;text-decoration:none;\" href=\"javascript:popPDF('M','" + idProducto + "','" + strProducto + "','','','');\"><img style=\"border:0;height:15px;\" src=\"Imagenes/impresora.png\" ></a> </div> </div>";
                html += "<div  style=\"clear:both;margin-top: 0px;margin-bottom: 10px; background-color:#761210; height:2px;\"></div>";
                string alt = "";
                if (idProducto == "1")
                {
                    html += "<div style=\"padding: 0px 92px 0px 92px;\">";
                    alt = "100";
                }
                else
                {
                    html += "<div style=\"padding: 0px 12px 0px 12px;\">";
                    alt = "82";
                }

                html += "<table cellpadding=\"1\" cellspacing=\"4\" style=\"border-style:solid;";
                html += "border-width:3px; border-color:#f2f1ed;\"><tr>";
                foreach (DataColumn dc in dt.Columns)
                {
                    html += "<td style=\"width:82px; height: 20px; text-align: center;color: black; font-size:9px;font-family:Arial;\">" + dc.ColumnName + "</TD>";
                }
                //html += "<td style=\"height: 20px; text-align: center;color: black;\">FECHA</TD></tr></table>";
                html += "</tr></table>";
                int c = dt.Columns.Count;

                foreach (DataRow dr in dt.Rows)
                {
                    html += "<div style=\"margin-top: 0px; background-color: #f2f1ed; padding-top: 5px; padding-bottom: 5px;\">";
                    html += "<table cellpadding=\"1\" cellspacing=\"4\" style=\"background-color:white ;border-style:solid;";
                    html += "border-width:3px; border-color:White;\"><tr>";
                    for (int i = 0; i < c; i++)
                    {

                        html += "<td style=\"width:82px; height: 20px;";
                        if (i == 0 || (idProducto == "1" && i == 4))
                        {
                            html += "background-color: white; color: black;";
                        }
                        if (i == 1)
                        {
                            html += "background-color: #58595B; color: white;";
                        }
                        if (i > 1 && ((idProducto == "1" && i != 4) || (idProducto != "1" && i != 6)))
                        {
                            html += "background-color: #DCDDDF; color: black;";
                        }
                        html += "font-size:9px;text-align: center;font-family:Arial;\">";

                        switch (dr[i].GetType().Name)
                        {
                            case "Decimal":
                                html += Convert.ToDecimal(dr[i]).ToString("#,##0.00");
                                break;
                            default:
                                html += dr[i];
                                break;

                        }

                        
                        html += "</td>";
                        
                    }
                    html += "</tr></table></div>";

                }
                html += "</div></div>";

                html = HistoricoMervaros() + html;

                cn.Cerrar();

                return html;
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }

        public static string HistoricoMervaros()
        {
            string html = "<div id=\"HistoricoCotizaciones\" style=\"margin-top: -35px;margin-bottom: 12px;\">";
               html+="<div class=\"TITULOSECCIONPARAMETRO\">";
               html+="<div style=\"float: left; text-align: center; width: 100%;\">";
               html+="<span>HISTORICO DE COTIZACIONES</span>";
               html+="</div></div>";
               html+="<div style=\"background-color: #ededed; padding: 6px; text-align: center;\">";
               html+="<span Class=\"lbl\">Fecha:</span>";                        
               html+="<input id=\"txtFechaMervaros\" class=\"txt\" />";
               html+="<input id=\"Button1\" type=\"button\" value=\"ok\" onclick=\"javascript:HistoricoMervaRos();\" style=\"font-size: x-small;";
               html+="width: 26px; height: 20px;\" /></div></div>";
               return html;
        }


        #endregion
        #region MONEDAS

        [WebMethod]
        public static string CotizacionEstadisticas(string idMoneda)
        {
            var cn = new ClsConeccion();
            cn.Abrir();
            cn.AgregarParametros("intMoneda", Convert.ToInt32(idMoneda));
            var sqldr2 = cn.EjecutarSpDataReader("sp_ADM_GETCONFIGURACIONMONEDA");
            var dt1 = new DataTable();
            dt1.Load(sqldr2);
            
            cn.Cerrar();

            return dt1.Rows[0]["Estadisticas"].ToString();

        }
        [WebMethod]
        public static string Cotizacion(string idMoneda)
        {
            try
            {
                var cn = new ClsConeccion();
                cn.Abrir();
                cn.AgregarParametros("intMoneda", Convert.ToInt32(idMoneda));
                var sqldr2 = cn.EjecutarSpDataReader("sp_ADM_GETCONFIGURACIONMONEDA");
                var dt1 = new DataTable();
                dt1.Load(sqldr2);
                string mon = dt1.Rows[0]["varMoneda"].ToString().ToUpper();
                string imgMoneda = dt1.Rows[0]["IMAGEN"].ToString();
                string colorCompra = dt1.Rows[0]["Color1"].ToString();
                string colorVenta = dt1.Rows[0]["Color2"].ToString();

                cn.Cerrar();

                cn = new ClsConeccion();
                cn.Abrir();

                //cn.AgregarParametros("intMoneda", Convert.ToInt32(idMoneda));
                //var sqldr = cn.EjecutarSpDataReader("sp_ADM_COTIZACIONESMONEDA_Version2");
                string sqlQuery = "SELECT * from CotizacionesActuales WHERE idMoneda = " + idMoneda;
                var sqldr = cn.EjecutarSqlDataReader(sqlQuery);

                var dt = new DataTable();
                dt.Load(sqldr);

                string html = "";

                if (dt.Rows.Count > 0)
                {
                    html += "<div  style=\" font-family:Arial; margin-top: 0px; background-image: url(" + imgMoneda + ");";
                    html += "height: 112px; padding-top: 15px; padding-right: 5px;  padding-left: 5px;\">";
                    html += "<div style=\"height:18px;\"><div style=\"float:left;font-family:Arial;font-size:15px; color:white;\"> cotización del día / " + mon + " </div> ";
                    html += "<div style=\"float:right\"><a style=\"border:0;color:black;text-decoration:none;\" href=\"javascript:popPDF('C','" + idMoneda + "','','','','');\"><img style=\"border:0;height:15px;\" src=\"Imagenes/impresora.png\" ></a> </div> </div>";
                    html += "<table style=\"margin-top: 5px; height: 86px; clear:both;\" > <tr> <td  style=\"text-align: center;\"> PROMEDIOS(*) </td> <td style=\"text-align: center;\"> COMPRA </td> <td style=\"text-align: center;\"> VENTA </td><td></td></tr>";

                    string sql = "Select varBanco, varSucursal, varPagina, convert(varchar, datfechaIngreso  , 103) fecha, ";
                    sql += "(CASE WHEN monCompra= 0 THEN '-' ELSE '$' + Replace (Cast(Cast(monCompra as Numeric(9,3)) as varchar),'.',',')   END) as monCompra , ";
                    sql += "(CASE WHEN monVenta = 0 THEN '-' ELSE '$' + Replace (Cast(Cast(monVenta as Numeric(9,3)) as varchar),'.',',')   END) as monVenta ";
                    sql += "FROM Cotizaciones C INNER JOIN Bancos B ON C.idBanco = B.idBanco and B.intOrden = 99999 ";
                    sql += "WHERE datFechaIngreso = (Select MAX(datFechaIngreso) FROM Cotizaciones C INNER JOIN Bancos B ON C.idBanco = B.idBanco and B.intOrden = 99999 AND C.idMoneda =  " + idMoneda +  ") ";
                    

                    var sqldrBlue = cn.EjecutarSqlDataReader(sql);

                    var dtBlue = new DataTable();
                    
                    dtBlue.Load(sqldrBlue);

                    if (dtBlue.Rows.Count > 0)
                    { 
                    html += " <tr> <td style=\" font-family:Arial; font-size:12px;width: 170px; background-color: " + colorCompra + " ; text-align: center; color: White;\">";
                    html += "<a target=\"_blank\" style=\"border:0;color:white;text-decoration:none;\" href=\"http://" + dtBlue.Rows[0]["varPagina"].ToString() + "\"  class=\"show-tooltip\" title=\"" + dtBlue.Rows[0]["varSucursal"].ToString() + "\">";
                    html += dtBlue.Rows[0]["varBanco"].ToString() +"</a>";
                    html += " </td> <td style=\" font-size:12px; width: 60px;  background-color:" + colorCompra + ";text-align: center; color: White;\">";
                    html += dtBlue.Rows[0]["monCompra"].ToString();
                    html += " </td> <td style=\"font-size:12px; width: 60px; background-color: " + colorVenta + ";text-align: center; color: White;\">";
                    html += dtBlue.Rows[0]["monVenta"].ToString();
                    html += "</td><td style=\"font-size:10px;\">" + dtBlue.Rows[0]["fecha"].ToString() + "</td></tr>";
                    }



                    for (int c = 0; c <= 1; c++)
                    {
                        html += " <tr> <td style=\" font-family:Arial; font-size:12px;width: 170px; background-color: " + colorCompra + " ; text-align: center; color: White;\">";
                        html += dt.Rows[c]["varbanco"].ToString();
                        html += " </td> <td style=\" font-size:12px; width: 60px;  background-color:" + colorCompra + ";text-align: center; color: White;\">";
                        html += dt.Rows[c]["monCompra"].ToString();
                        html += " </td> <td style=\"font-size:12px; width: 60px; background-color: " + colorVenta + ";text-align: center; color: White;\">";
                        html += dt.Rows[c]["monVenta"].ToString();
                        html += "</td><td style=\"font-size:10px;\">" + dt.Rows[c]["FECHA"].ToString().Substring(0, 10) + "</td></tr>";

                    }
                    html += "</table></div>";
                    html += "<div id=\"datos\" style=\" font-family:Arial;margin-top: 0px; background-color: #f2f1ed; padding: 5px;\">";
                    for (int c = 2; dt.Rows.Count - 1 >= c; c++)
                    {
                        html += "<div style=\"margin-top: 0px; background-color: #f2f1ed; padding-top: 0px; padding-bottom: 5px;\">";
                        html += "<table width=\"100%\" cellpadding=\"1\" cellspacing=\"2\" style=\"background-color: white;border-style:solid;";
                        html += "border-width:3px; border-color:White;\"><tr><td style=\"width: 167px; height: 20px; background-color: white; text-align: center;color: black;\">";

                        html += "<a target=\"_blank\" style=\"border:0;color:black;text-decoration:none;\" href=\"http://" + dt.Rows[c]["varlink"].ToString() + "\" class=\"show-tooltip\" title=\"" + dt.Rows[c]["varSucursal"].ToString() + "\">";
                        if (dt.Rows[c]["varimg"].ToString().Length > 0)
                        { html += "<img style=\"border:0;\" src=Administracion/Logos/" + dt.Rows[c]["varimg"].ToString() + " width=150px/></a>"; }
                        else
                        { html += dt.Rows[c]["varbanco"].ToString() + "</a>"; }

                        string ColorVenta = "";
                        string ColorCompra = "";
                        if (DBNull.Value == dt.Rows[c]["promedioCompra"])
                        { ColorCompra = "#7f7f80"; }
                        else
                        {
                            //ColorCompra="#dfcf93";
                            ColorCompra = "#624036";
                        }

                        if (DBNull.Value == dt.Rows[c]["promedioVenta"])
                        { ColorVenta = "#7f7f80"; }
                        else
                        { ColorVenta = "#624036"; }


                        html += "</td><td style=\"width: 55px; height: 20px; background-color:" + ColorCompra + "; padding: 3px; text-align: center;color: White;\">";
                        html += dt.Rows[c]["monCompra"].ToString();
                        html += "</td><td style=\"width: 55px; height: 20px; background-color: " + ColorVenta + "; padding: 3px; text-align: center;color: White;\">";
                        html += dt.Rows[c]["monVenta"].ToString();
                        html += "</td><td style=\"font-size:10px;\">" + dt.Rows[c]["FECHA"].ToString() + "</td></tr></table></div>";
                    }
                    html += "</div>";

                }

                cn.Cerrar();

                return html;
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }
        [WebMethod]
        public static string HistoricoBanco(string idMoneda, string txtFechaDesde, string txtFechaHasta, string Banco1)
        {
            var cn = new ClsConeccion();
            cn.Abrir();
            cn.AgregarParametros("intMoneda", Convert.ToInt32(idMoneda));
            var sqldr2 = cn.EjecutarSpDataReader("sp_ADM_GETCONFIGURACIONMONEDA");
            var dt1 = new DataTable();
            dt1.Load(sqldr2);
            string mon = dt1.Rows[0]["varMoneda"].ToString().ToUpper();
            string imgMoneda = dt1.Rows[0]["IMAGEN"].ToString();
            string colorCompra = dt1.Rows[0]["Color1"].ToString();
            string colorVenta = dt1.Rows[0]["Color2"].ToString();
            cn.Cerrar();

            cn = new ClsConeccion();
            cn.Abrir();

            cn.AgregarParametros("intMoneda", Convert.ToInt32(idMoneda));
            var sqldr = cn.EjecutarSpDataReader("sp_ADM_COTIZACIONESMONEDA");


            var dt = new DataTable();
            dt.Load(sqldr);

            string html = "";

            if (dt.Rows.Count > 0)
            {
                html += "<div  style=\" font-family:Arial; margin-top: 0px; background-image: url(" + imgMoneda + ");";
                html += "height: 112px; padding-top: 15px; padding-right: 5px;  padding-left: 5px;\">";
                html += "<div style=\"height:18px;\"><div style=\"float:left;font-family:Arial;font-size:15px; color:white;\"> cotización del día / " + mon + " </div> ";
                html += "<div style=\"float:right\"><a style=\"border:0;color:black;text-decoration:none;\" href=\"javascript:popPDF('HB','" + idMoneda + "','" + txtFechaDesde + " ','" + txtFechaHasta + "','" + Banco1 + "','');\"><img style=\"border:0;height:15px;\" src=\"Imagenes/impresora.png\" ></a> </div> </div>";
                html += "<table style=\"margin-top: 5px; height: 86px; clear:both;\" > <tr> <td  style=\"text-align: center;\"> PROMEDIOS(*) </td> <td style=\"text-align: center;\"> COMPRA </td> <td style=\"text-align: center;\"> VENTA </td><td></td></tr>";
                for (int c = 0; c <= 1; c++)
                {
                    html += " <tr> <td style=\"font-size:12px;width: 170px;height: 20px; background-color: " + colorCompra + " ; text-align: center; color: White;\">";
                    html += dt.Rows[c]["varbanco"].ToString();
                    html += " </td> <td style=\" font-size:12px; width: 60px;  background-color:" + colorCompra + ";text-align: center; color: White;\">";
                    html += dt.Rows[c]["monCompra"].ToString();
                    html += " </td> <td style=\"font-size:12px; width: 60px; background-color: " + colorVenta + ";text-align: center; color: White;\">";
                    html += dt.Rows[c]["monVenta"].ToString();
                    html += "</td><td style=\"font-size:10px;\">" + dt.Rows[c]["FECHA"].ToString().Substring(0, 10) + "</td></tr>";

                }
            }
            html += "</table></div>";


            cn.Cerrar();
            cn = new ClsConeccion();
            cn.Abrir();
            cn.AgregarParametros("moneda", (idMoneda));
            cn.AgregarParametros("Banco1", varbanco(Convert.ToInt32(Banco1)));
            cn.AgregarParametros("Desde", (FormatoAnsiFecha(txtFechaDesde) + " 00:00:00"));
            cn.AgregarParametros("Hasta", (FormatoAnsiFecha(txtFechaHasta) + " 23:59:59"));

            sqldr = cn.EjecutarSpDataReader("spGetHistorico");
            dt = new DataTable();
            dt.Load(sqldr);
            if (dt.Rows.Count > 0)
            {
                html += "<div  style=\"margin-top: 0px;background-color: #f2f1ed; padding: 5px 5px 5px 5px;\"> <div class=\"TituloDatos\">cotizaciones " + varbanco(Convert.ToInt32(Banco1)) + " /" + Moneda(Convert.ToInt32(idMoneda)) + "</div>";
                html += "<table style=\"background-color: white;width:100%;\">";
                foreach (DataRow dr in dt.Rows)
                {
                    var dbVenta = (dr["Banco1Venta"] == Convert.DBNull) ? "" : Convert.ToDouble(dr["Banco1Venta"]).ToString("f3");
                    var dbCompra = (dr["Banco1Compra"] == Convert.DBNull) ? "" : Convert.ToDouble(dr["Banco1Compra"]).ToString("f3"); 
                    html += "<tr style=\"border-bottom:dotted 1px gray;\"> ";
                    html += "<td style=\"border-bottom:dotted 1px gray; height:18px;\" width=\"170px\">" + varbanco(Convert.ToInt32(Banco1)) + "</TD>";
                    html += "<td style=\"border-bottom:dotted 1px gray; text-align:center;\" width=\"60px\"> " + dbCompra + " </TD>";
                    html += "<td style=\"border-bottom:dotted 1px gray; text-align:center;\" width=\"60px\"> " + dbVenta + " </TD>";
                    html += "<td style=\"border-bottom:dotted 1px gray; text-align:center;\">" + dr["Fecha"].ToString() + "</TD>";
                    html += "</tr>";
                }
                html += "</table></div>";

            }
            else
            {

                html += "<div><br>";
                html += "NO EXISTEN DATOS PARA ESTE PERIODO </DIV>";

            }

            cn.Cerrar();

            return html;

        }
        [WebMethod]
        public static string Comparar(string idMoneda, string txtFechaDesde, string txtFechaHasta, string Banco1, string Banco2)
        {
            var cn = new ClsConeccion();
            cn.Abrir();
            cn.AgregarParametros("intMoneda", Convert.ToInt32(idMoneda));
            var sqldr2 = cn.EjecutarSpDataReader("sp_ADM_GETCONFIGURACIONMONEDA");
            var dt1 = new DataTable();
            dt1.Load(sqldr2);
            string mon = dt1.Rows[0]["varMoneda"].ToString().ToUpper();
            string imgMoneda = dt1.Rows[0]["IMAGEN"].ToString();
            string colorCompra = dt1.Rows[0]["Color1"].ToString();
            string colorVenta = dt1.Rows[0]["Color2"].ToString();
            cn.Cerrar();

            cn = new ClsConeccion();
            cn.Abrir();

            cn.AgregarParametros("intMoneda", Convert.ToInt32(idMoneda));
            var sqldr = cn.EjecutarSpDataReader("sp_ADM_COTIZACIONESMONEDA");


            var dt = new DataTable();
            dt.Load(sqldr);

            string html = "";

            if (dt.Rows.Count > 0)
            {
                html += "<div  style=\" font-family:Arial; margin-top: 0px; background-image: url(" + imgMoneda + ");";
                html += "height: 112px; padding-top: 15px; padding-right: 5px;  padding-left: 5px;\">";
                html += "<div style=\"height:18px;\"><div style=\"float:left;font-family:Arial;font-size:15px; color:white;\"> cotización del día / " + mon + " </div> ";
                html += "<div style=\"float:right\"><a style=\"border:0;color:black;text-decoration:none;\" href=\"javascript:popPDF('CH','" + idMoneda + "','" + txtFechaDesde + " ','" + txtFechaHasta + "','" + Banco1 + "','" + Banco2 + "');\"><img style=\"border:0;height:15px;\" src=\"Imagenes/impresora.png\" ></a> </div> </div>";
                html += "<table style=\"margin-top: 5px; height: 86px; clear:both;\" > <tr> <td  style=\"text-align: center;\"> PROMEDIOS(*) </td> <td style=\"text-align: center;\"> COMPRA </td> <td style=\"text-align: center;\"> VENTA </td><td></td></tr>";
                for (int c = 0; c <= 1; c++)
                {
                    html += " <tr> <td style=\"font-size:12px;width: 170px; background-color: " + colorCompra + " ; text-align: center; color: White;\">";
                    html += dt.Rows[c]["varbanco"].ToString();
                    html += " </td> <td style=\" font-size:12px; width: 60px;  background-color:" + colorCompra + ";text-align: center; color: White;\">";
                    html += dt.Rows[c]["monCompra"].ToString();
                    html += " </td> <td style=\"font-size:12px; width: 60px; background-color: " + colorVenta + ";text-align: center; color: White;\">";
                    html += dt.Rows[c]["monVenta"].ToString();
                    html += "</td><td style=\"font-size:10px;\">" + dt.Rows[c]["FECHA"].ToString().Substring(0, 10) + "</td></tr>";

                }
            }
            html += "</table></div>";


            cn.Cerrar();
            cn = new ClsConeccion();
            cn.Abrir();
            cn.AgregarParametros("moneda", (idMoneda));
            cn.AgregarParametros("Banco1", varbanco(Convert.ToInt32(Banco1)));
            cn.AgregarParametros("Banco2", varbanco(Convert.ToInt32(Banco2)));
            cn.AgregarParametros("Desde", (FormatoAnsiFecha(txtFechaDesde) + " 00:00:00"));
            cn.AgregarParametros("Hasta", (FormatoAnsiFecha(txtFechaHasta) + " 23:59:59"));

            sqldr = cn.EjecutarSpDataReader("spGetComparacion");
            dt = new DataTable();
            dt.Load(sqldr);
            if (dt.Rows.Count > 0)
            {
                html += "<div  style=\"margin-top: 0px;background-color: #f2f1ed; padding: 5px 5px 5px 5px;\"> <div class=\"TituloDatos\">comparación de entidades " +   " /" + Moneda(Convert.ToInt32(idMoneda)) + "</div>";
                html += "<table style=\"background-color: white;width:100%;\">";
                html += "<tr><td style=\"border-bottom:dotted 1px gray; height:18px;text-align:center;\" colspan=\"2\">" + varbanco(Convert.ToInt32(Banco1)) + "</td><td style=\"border-bottom:dotted 1px gray;text-align:center; height:18px;\" colspan=\"2\">" + varbanco(Convert.ToInt32(Banco2)) + "</td><td style=\"border-bottom:dotted 1px gray; height:18px;\"></td></tr>";
                html += "<td style=\"border-bottom:dotted 1px gray; text-align:center;height:18px;\">" + "compra" + "</td>";
                html += "<td style=\"border-bottom:dotted 1px gray;text-align:center; height:18px;\">" + "venta" + "</td>";
                html += "<td style=\"border-bottom:dotted 1px gray;text-align:center; height:18px;\">" + "compra" + "</td>";
                html += "<td style=\"border-bottom:dotted 1px gray; text-align:center;height:18px;\">" + "venta" + "</td>";
                html += "<td style=\"border-bottom:dotted 1px gray;text-align:center; height:18px;\">" + "fecha" + "</td></tr>";
                
                foreach (DataRow dr in dt.Rows)
                {
                    html += "<tr style=\"border-bottom:dotted 1px gray;\"> ";
                    html += "<td style=\"border-bottom:dotted 1px gray; width:80px; text-align:center; height:18px;\">" + Convert.ToDouble(dr["Banco1Compra"]).ToString("f4") + "</td>";
                    html += "<td style=\"border-bottom:dotted 1px gray; width:80px; text-align:center;height:18px;\">" + Convert.ToDouble(dr["Banco1Venta"]).ToString("f4") + "</td>";
                    html += "<td style=\"border-bottom:dotted 1px gray; width:80px; text-align:center;height:18px;\">" + Convert.ToDouble(dr["Banco2Compra"]).ToString("f4") + "</td>";
                    html += "<td style=\"border-bottom:dotted 1px gray; width:80px; text-align:center; height:18px;\">" + Convert.ToDouble(dr["Banco2Venta"]).ToString("f4") + "</td>";
                    html += "<td style=\"border-bottom:dotted 1px gray; height:18px;\">" + dr["Fecha"].ToString().Substring(0,10) + "</td>";
                    html += "</tr>";
                }
                html += "</table></div>";

            }
            else
            {

                html += "<div><br>";
                html += "NO EXISTEN DATOS PARA ESTE PERIODO </DIV>";

            }

            cn.Cerrar();

            return html;

        }
        public static string varbanco(int idbanco)
        {
            if (idbanco==0)
            {
                return "PROMEDIO ROSARIO FINANZAS";
            }
            if (idbanco == -1)
            {
                return "PROMEDIO SIMPLE";
            }

            var cn = new ClsConeccion();
            cn.Abrir();
            cn.AgregarParametros("idBanco", idbanco);
            var sqldr = cn.EjecutarSpDataReader("sp_ADM_GetBanco");
            var dt = new DataTable();
            dt.Load(sqldr);
            
            cn.Cerrar();

            return dt.Rows[0]["varBanco"].ToString();

        }


        [WebMethod]
        public static string Historico(string idMoneda, string txtFecha)
        {

            var cn = new ClsConeccion();
            cn.Abrir();
            cn.AgregarParametros("intMoneda", Convert.ToInt32(idMoneda));
            var sqldr2 = cn.EjecutarSpDataReader("sp_ADM_GETCONFIGURACIONMONEDA");
            var dt1 = new DataTable();
            dt1.Load(sqldr2);
            string mon = dt1.Rows[0]["varMoneda"].ToString().ToUpper();
            string imgMoneda = dt1.Rows[0]["IMAGEN"].ToString();
            string colorCompra = dt1.Rows[0]["Color1"].ToString();
            string colorVenta = dt1.Rows[0]["Color2"].ToString();
            cn.Cerrar();

            cn = new ClsConeccion();
            cn.Abrir();
            
            cn.AgregarParametros("intMoneda", Convert.ToInt32(idMoneda));
            var sqldr = cn.EjecutarSpDataReader("sp_ADM_COTIZACIONESMONEDA");
            
            
            var dt = new DataTable();
            dt.Load(sqldr);

            string html = "";

            if (dt.Rows.Count > 0)
            {
                html += "<div  style=\" font-family:Arial; margin-top: 0px; background-image: url(" + imgMoneda + ");";
                html += "height: 112px; padding-top: 15px; padding-right: 5px;  padding-left: 5px;\">";
                html += "<div style=\"height:18px;\"><div style=\"float:left;font-family:Arial;font-size:15px; color:white;\"> cotización del día / " + mon + " </div> ";
                html += "<div style=\"float:right\"><a style=\"border:0;color:black;text-decoration:none;\" href=\"javascript:popPDF('H','" + idMoneda + "','" + txtFecha + "','','','');\"><img style=\"border:0;height:15px;\" src=\"Imagenes/impresora.png\" ></a> </div> </div>";
                html += "<table style=\"margin-top: 5px; height: 86px; clear:both;\" > <tr> <td  style=\"text-align: center;\"> PROMEDIOS(*) </td> <td style=\"text-align: center;\"> COMPRA </td> <td style=\"text-align: center;\"> VENTA </td><td></td></tr>";
                for (int c = 0; c <= 1; c++)
                {
                    html += " <tr> <td style=\"font-size:12px;width: 170px; background-color: " + colorCompra + " ; text-align: center; color: White;\">";
                    html += dt.Rows[c]["varbanco"].ToString();
                    html += " </td> <td style=\" font-size:12px; width: 60px;  background-color:" + colorCompra + ";text-align: center; color: White;\">";
                    html += dt.Rows[c]["monCompra"].ToString();
                    html += " </td> <td style=\"font-size:12px; width: 60px; background-color: " + colorVenta + ";text-align: center; color: White;\">";
                    html += dt.Rows[c]["monVenta"].ToString();
                    html += "</td><td style=\"font-size:10px;\">" + dt.Rows[c]["FECHA"].ToString().Substring(0, 10) + "</td></tr>";

                }
            }
                html += "</table></div>";

              
                cn.Cerrar();
                cn = new ClsConeccion();
                cn.Abrir();
                cn.AgregarParametros("intMoneda", Convert.ToInt32(idMoneda));
                cn.AgregarParametros("datFechaDesde", Convert.ToDateTime(txtFecha + " 00:00:00"));
                cn.AgregarParametros("datFechaHasta", Convert.ToDateTime(txtFecha + " 23:59:59"));
                
                sqldr = cn.EjecutarSpDataReader("sp_ADM_COTIZACIONESMONEDAFecha");
                dt = new DataTable();
                dt.Load(sqldr);
                if (dt.Rows.Count > 0)
                {
                    html += "<div  style=\"margin-top: 0px;background-color: #f2f1ed; padding: 5px 5px 5px 5px;\"> <div class=\"TituloDatos\">cotizaciones del día " + txtFecha + " " + Moneda(Convert.ToInt32(idMoneda)) + "</div>";
                    html += "<table style=\"background-color: white;width:100%;\">"; 
                    foreach (DataRow dr in dt.Rows)
                    {
                        html += "<tr style=\"border-bottom:dotted 1px gray;\"> ";
                        html += "<td style=\"border-bottom:dotted 1px gray; height:40px;\" width=\"167px\">" + dr["varBanco"].ToString() + "</TD>";
                        html += "<td style=\"border-bottom:dotted 1px gray; text-align:center;\" width=\"55px\"> " + dr["monCompra"].ToString() + " </TD>";
                        html += "<td style=\"border-bottom:dotted 1px gray; text-align:center;\" width=\"55px\"> " + dr["monVenta"].ToString() + " </TD>";
                        html += "<td style=\"border-bottom:dotted 1px gray; text-align:center;\">" + dr["Fecha"].ToString().Substring(11, 5) + "</TD>";
                    html += "</tr>";
                    }
                    html += "</table></div>";

                }
                else
                {
                    
                    html += "<div><br>";
                    html += "NO EXISTEN DATOS PARA ESTE PERIODO </DIV>";
            
                }

                cn.Cerrar();

                return html;
        }
        public static string Moneda(int _Moneda)
        {
            var cn = new ClsConeccion();
            cn.Abrir();
            cn.AgregarParametros("idMoneda", _Moneda);
            var sqldr = cn.EjecutarSpDataReader("sp_ADM_GetMoneda");
            var dt = new DataTable();
            dt.Load(sqldr);

            cn.Cerrar();

            return dt.Rows[0]["varMoneda"].ToString();

        }
        #endregion

        public static string FormatoAnsiFecha(string fecha)
        {
            string anio = fecha.Substring(6, 4);
            string mes = fecha.Substring(3, 2);
            string dia = fecha.Substring(0, 2);
            fecha = anio + mes + dia;
            return fecha;

        }

    }
}
